package com.FH.applyFrom.finance_budget;


import com.FH.applyForm.officeServies.FinanceBudget.FinanceBudgetPullDTO;
import com.FH.applyForm.officeServies.FinanceBudget.FinanceBudgetsResource;
import com.FH.applyForm.officeServies.advance.AdvanceResource;
import com.FH.organization.operator_log.OperatorLogPullDTO;
import com.FH.organization.operator_log.OperatorLogResource;
import com.FH.organization.suggestion.SuggestionResource;
import com.FH.organization.work_flow.WorkFlowPullDTO;
import com.FH.organization.work_flow.WorkFlowResource;
import com.FH.util.AccessDB;
import com.FH.util.ResultDTO;
import com.FH.util.warnSrc;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;

import javax.ejb.Stateless;
import javax.inject.Inject;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * Created by wlb on 2016/7/15.
 */
@Stateless
public class finance_budgetDAOImpl extends AccessDB implements finance_budgetDAO {


    /**
     * 财务预算获取信息
     *
     * @param apply
     * @param page
     * @param size
     * @return
     */


    @Inject
    OperatorLogResource operatorLogResource;
    @Inject
    WorkFlowResource workFlowResource;
    @Inject
    finance_budgetResource finance_budgetResource;
    @Inject
    AdvanceResource advanceResource;

    @Inject
    FinanceBudgetsResource financeBudgetsResource;

    @Inject
    SuggestionResource suggestionResource;

    @Override
    public ResultDTO getfinance_budgetInfo(String apply, String page, String size) {

        ResultDTO resultDTO = new ResultDTO();

        try {
            JSONObject json = new JSONObject(apply);
            String existName = "0";

            //先查询数据库中是否存在字段
            String sqlexit = "select count(2) from finance_budget  where 1=1 ";

            String sql_w = "";//查询条件sql组合
            String sql_page = "";//分页组合

            Integer page_ok = Integer.parseInt(page);//将定义page、size的String类型转换成Integer类型
            Integer size_ok = Integer.parseInt(size);

            if (page.equals("1")) {
                sql_page = " limit 0," + size + "";
            } else {
                sql_page = " limit " + ((page_ok - 1) * size_ok) + "," + size + "";
            }

            if (json.has("member_id")) {
                sql_w += " AND member_id ='" + json.getString("member_id") + "'";
            }
            if (json.has("finance_budget_title")) {
                sql_w += " AND finance_budget_title like '%" + json.getString("finance_budget_title") + "%'";
            }
            if (json.has("finance_budget_id")) {
                sql_w += " AND finance_budget_id ='" + json.getString("finance_budget_id") + "'";
            }

            if (json.has("isverify")) {
                sql_w += " AND isverify ='" + json.getString("isverify") + "'";
            }
//            else {
//                sql_w += " AND (isverify IS NULL OR isverify=0)";
//            }


            String sql_ok = sqlexit + sql_w + sql_page;//组合好的sql语句
            existName = getReportingToOne(sqlexit + sql_w);

            List<String[]> seresult = new ArrayList<String[]>();

            List<finance_budgetShowDTO> result = new ArrayList<finance_budgetShowDTO>();

            if (!existName.equals("0")) {
                String sql = "select finance_budget_id, organization_id , finance_budget_note, member_id,parent_finance_budget,isverify,update_time,finance_budget_starttime,fail_resource,office_first,office_second" +
                        " FROM finance_budget where 1=1  " + sql_w + " ORDER BY finance_budget_starttime DESC" + sql_page;
                //     String sql ="select * from finance_budget" ;
                seresult = this.getReportingTo(sql);

                //构建返回对象
                for (int i = 0; i < seresult.size(); i++) {

                    finance_budgetShowDTO fbs = new finance_budgetShowDTO();

                    fbs.setFinance_budget_id(seresult.get(i)[1]);
                    fbs.setOrganization_id(seresult.get(i)[2]);
                    fbs.setFinance_budget_note(seresult.get(i)[3]);
                    fbs.setMember_id(seresult.get(i)[4]);
                    fbs.setParent_finance_budget(seresult.get(i)[5]);
                    fbs.setIsverify(seresult.get(i)[6]);
                    fbs.setUpdate_time(seresult.get(i)[7]);
                    fbs.setFinance_budget_starttime(seresult.get(i)[8]);
                    fbs.setFail_resource(seresult.get(i)[9]);
                    fbs.setOffice_first(seresult.get(i)[10]);
                    fbs.setOffice_second(seresult.get(i)[11]);
                    ResultDTO worklist = null, flowpath = null,suggestion=null;

                    if (json.has("isverify")) {
                        String state = json.getString("isverify");
                        worklist = workFlowResource.getWorkFlowInfo("{work_flow_formid:" + seresult.get(i)[1] + ",work_flow_state:" + state + "}", "1", "20");
                    } else {
                        worklist = workFlowResource.getWorkFlowInfo("{work_flow_formid:" + seresult.get(i)[1] + "}", "1", "20");
                    }

                    fbs.setWs(worklist.getValues());

                    flowpath = financeBudgetsResource.getFinanceBudgetInfo("{finance_budget_id:" + seresult.get(i)[1] + "}", "1", "1000");
                    fbs.setQq(flowpath.getValues());

                    if(seresult.get(i)[1] != null && ! seresult.get(i)[1].equals("")){

                        suggestion=suggestionResource.getSuggestionInfo("{form_id:" + seresult.get(i)[1] + "}", "1", "100");
                        fbs.setSuggestionShowDTOs(suggestion.getValues());
                    }

                    result.add(fbs);


                }

//
            }
            if (seresult.size() != 0) {
                resultDTO.setStatus("90000");
                resultDTO.setDescritpion(warnSrc.get_success);
            } else {
                resultDTO.setStatus("90001");
                resultDTO.setDescritpion(warnSrc.get_fail);
            }
            resultDTO.setStr(result.size() + "");
            resultDTO.setValues(result);

        } catch (JSONException e) {
            e.printStackTrace();

        }
        return resultDTO;
    }

    /**
     * 增加报销单信息实现类
     *
     * @param dataDTO
     * @return
     */
    @Override
    public ResultDTO addfinance_budgetInfo(finance_budgetPullDTO dataDTO) {

        ResultDTO resultDTO = new ResultDTO();//构建返回对象

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = df.format(new Date());

        String uuid = UUID.randomUUID().toString().trim().replaceAll("-", "");

        String existName = "";
        /**
         * 判断报销申请单中是否已存在此数据
         *
         * 若存在  进行更新
         *
         * 如不存在 执行add方法
         *
         */
        String finance_budget_id = dataDTO.getFinance_budget_id();
        if (finance_budget_id != null && !finance_budget_id.equals("")) {
            return updatefinance_budgetInfo(dataDTO);
        }

        String QUERY = "";

        QUERY += "insert into finance_budget(finance_budget_id,organization_id,finance_budget_note,member_id,parent_finance_budget,isverify,finance_budget_starttime,fail_resource,office_first,office_second) values" +
                " ('" + uuid + "','" + dataDTO.getOrganization_id() + "','"
                + dataDTO.getFinance_budget_note() + "','" + dataDTO.getMember_id() + "','"
                + dataDTO.getParent_finance_budget() + "','" + 0 + "','" + time + "','" + " " + "','" + dataDTO.getOffice_first() + "','" + dataDTO.getOffice_second() + "')";


        int result = this.addReportingTo(QUERY);
        // 判断SQl是否执行成功
        if (result != 0) {
            resultDTO.setStr(uuid);
            resultDTO.setStatus("90000");//
            resultDTO.setDescritpion(warnSrc.add_success);

            /**
             * JSON解析集合、参会人
             */
            if (null != dataDTO.getFinancetype() && !"".equals(dataDTO.getFinancetype())) {
                String financetype = dataDTO.getFinancetype();
                try {
                    JSONArray array = new JSONArray(financetype);
                    if (array != null && array.length() > 0) {
                        List<finance_budgetPullDTO> results = new ArrayList<finance_budgetPullDTO>();
                        for (int i = 0; i < array.length(); i++) {
                            JSONObject jsonObject = array.getJSONObject(i);
                            String id = jsonObject.getString("id");
                            String name = jsonObject.getString("name");
                            String number = jsonObject.getString("selectnumber");
                            String price = jsonObject.getString("price");
                            FinanceBudgetPullDTO financeBudgetPullDTO1 = new FinanceBudgetPullDTO();
                            financeBudgetPullDTO1.setFinance_budget_id(uuid);
                            financeBudgetPullDTO1.setOffice_id(id);
                            financeBudgetPullDTO1.setOffice_name(name);
                            financeBudgetPullDTO1.setOfficetools_number(number);
                            financeBudgetPullDTO1.setOfficetools_price(price);
                            financeBudgetsResource.addFinanceBudgetInfo(financeBudgetPullDTO1);
                        }
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                }

            }


            //日志操作
            OperatorLogPullDTO operatorlogPullDTO = new OperatorLogPullDTO();
            operatorlogPullDTO.setWork_form_id(uuid);
            operatorlogPullDTO.setOperator_log_type("财务预算申请");
            operatorlogPullDTO.setOperator_log_title(dataDTO.getFinance_budget_title());
            operatorlogPullDTO.setOperator_log_content(dataDTO.getFinance_budget_desc());
            operatorlogPullDTO.setMember_id(dataDTO.getMember_id());
            operatorLogResource.addOperatorLogInfo(operatorlogPullDTO);

            //流程上报
            WorkFlowPullDTO workFlowPullDTO = new WorkFlowPullDTO();
            workFlowPullDTO.setWork_flow_formid(uuid);
            workFlowPullDTO.setWork_flow_type("finance_budget");
            workFlowPullDTO.setWork_flow_desc("财务预算申请请求审批");
            workFlowPullDTO.setWork_flow_date(time);
            workFlowPullDTO.setWork_flow_order("0");
            workFlowPullDTO.setWork_flow_createid(dataDTO.getMember_id());
            workFlowPullDTO.setWork_flow_approval(dataDTO.getReport_people());
            workFlowPullDTO.setWork_flow_approval_organization_id(dataDTO.getWork_flow_approval_organization_id());
            workFlowPullDTO.setFlow_path_id(dataDTO.getFlow_path_id());
            workFlowPullDTO.setWork_flow_suggestion("");
            workFlowResource.addWorkFlowInfo(workFlowPullDTO);
        } else {
            resultDTO.setStatus("90001");//
            resultDTO.setDescritpion(warnSrc.add_fail);
        }
        return resultDTO;
    }

    //关联表主键查询
    @Override
    public ResultDTO getfinanceInfo(String finance_budget_id, String page, String size) {

        ResultDTO resultDTO = new ResultDTO();

        try {
            JSONObject json = new JSONObject(finance_budget_id);
            String existName = "0";

            //先查询数据库中是否存在字段
            String sqlexit = "select count(2) from finance_budget  where 1=1 ";

            String sql_w = "";//查询条件sql组合
            String sql_page = "";//分页组合

            Integer page_ok = Integer.parseInt(page);//将定义page、size的String类型转换成Integer类型
            Integer size_ok = Integer.parseInt(size);

            if (page.equals("1")) {
                sql_page = " limit 0," + size + "";
            } else {
                sql_page = " limit " + ((page_ok - 1) * size_ok + 1) + "," + size + "";
            }

//            if (json.has("member_id")) {
//                sql_w += " AND member_id ='" + json.getString("member_id") + "'";
//            }
            if (json.has("finance_budget_title")) {
                sql_w += " AND finance_budget_title like '%" + json.getString("finance_budget_title") + "%'";
            }
            if (json.has("finance_budget_id")) {
                sql_w += " AND finance_budget_id ='" + json.getString("finance_budget_id") + "'";
            }

            if (json.has("isverify")) {
                sql_w += " AND isverify ='" + json.getString("isverify") + "'";
            } else {
                sql_w += " AND (isverify IS NULL OR isverify=0)";
            }


            String sql_ok = sqlexit + sql_w + sql_page;//组合好的sql语句
            existName = getReportingToOne(sql_ok);

            List<String[]> seresult = new ArrayList<String[]>();

            List<finance_budgetShowDTO> result = new ArrayList<finance_budgetShowDTO>();

            if (!existName.equals("0")) {
                String sql = " select f.finance_budget_id, f.organization_id,f.finance_budget_note, f.member_id,f.parent_finance_budget,f.isverify,f.finance_budget_starttime,f.fail_resource, r.organization_name FROM finance_budget AS f,organization AS r where 1=1 " + sql_w + sql_page;
                //     String sql ="select * from finance_budget" ;
                seresult = this.getReportingTo(sql);

                //构建返回对象
                for (int i = 0; i < seresult.size(); i++) {

                    finance_budgetShowDTO fbs = new finance_budgetShowDTO();

                    fbs.setFinance_budget_id(seresult.get(i)[1]);

                    fbs.setOrganization_id(seresult.get(i)[2]);
                    fbs.setFinance_budget_note(seresult.get(i)[3]);
                    fbs.setMember_id(seresult.get(i)[4]);
                    fbs.setParent_finance_budget(seresult.get(i)[5]);
                    fbs.setIsverify(seresult.get(i)[6]);
                    fbs.setFinance_budget_starttime(seresult.get(i)[7]);
                    fbs.setFail_resource(seresult.get(i)[8]);
                    fbs.setOrganization_name(seresult.get(i)[9]);

//
//                    ResultDTO worklist=null;
//
//                    if(json.has("isverify")){
//                        String state=json.getString("isverify");
//                        worklist=workFlowResource.getWorkFlowInfo("{work_flow_formid:"+seresult.get(i)[1]+",work_flow_state:"+state+"}","1","20");
//                    }else{
//                        worklist=workFlowResource.getWorkFlowInfo("{work_flow_formid:"+seresult.get(i)[1]+"}","1","20");
//                    }
//
//                    fbs.setWs(worklist.getValues());

                    result.add(fbs);
                }
            }
            if (seresult.size() != 0) {
                resultDTO.setStatus("90000");
                resultDTO.setDescritpion(warnSrc.get_success);
            } else {
                resultDTO.setStatus("90001");
                resultDTO.setDescritpion(warnSrc.get_fail);
            }
            resultDTO.setValues(result);

        } catch (JSONException e) {
            e.printStackTrace();

        }
        return resultDTO;
    }


    //获取父id
    @Override
    public ResultDTO getParent_finance_idInfo(String member_id) {


        ResultDTO resultDTO = new ResultDTO();

        List<String[]> seresult = null;

        //判断用户名是否存在
        if (null != member_id) {

            String sqlexit = "select finance_budget_id from finance_budget where member_id = '" + member_id + "' and (parent_finance_budget='NULL' OR parent_finance_budget='');";

            seresult = this.getReportingTo(sqlexit);

            if (seresult != null && seresult.size() > 0) {
                resultDTO.setStr(seresult.get(0)[1]);
            }

        }

        if (seresult.size() != 0) {
            resultDTO.setStatus("90000");//
            resultDTO.setDescritpion(warnSrc.get_success);
        } else {
            resultDTO.setStatus("90001");//
            resultDTO.setDescritpion(warnSrc.get_fail);
        }


        return resultDTO;
    }

    /**
     * 删除财务预算申请
     *
     * @param dataDTO
     * @return
     */
    @Override
    public ResultDTO deletefinance_budgetInfo(finance_budgetPullDTO dataDTO) {

        //删除之前查询该表是否有记录
        String sql = "select count(1) from finance_budget where finance_budget_id='" + dataDTO.getFinance_budget_id() + "'";
        String existName = getReportingToOne(sql);

        int result = 0;

        ResultDTO resultDTO = new ResultDTO();

        if (!existName.equals("0")) {

            String QUERY = "";

            QUERY += "delete from finance_budget where finance_budget_id='" + dataDTO.getFinance_budget_id() + "'";

            result = this.addReportingTo(QUERY);
        }

        if (result != 0) {
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(warnSrc.delete_success);

            FinanceBudgetPullDTO financeBudgetPullDTO = new FinanceBudgetPullDTO();
            financeBudgetPullDTO.setFinance_budget_id(dataDTO.getFinance_budget_id());
            financeBudgetsResource.deleteFinanceBudgetInfo(financeBudgetPullDTO);


            WorkFlowPullDTO workFlowPullDTO = new WorkFlowPullDTO();
            workFlowPullDTO.setWork_flow_formid(dataDTO.getFinance_budget_id());
            workFlowResource.deleteWorkFlowInfo(workFlowPullDTO);
        } else {
            resultDTO.setStatus("90001");
            resultDTO.setDescritpion(warnSrc.delete_fail);
        }
        return resultDTO;
    }

    /**
     * 更新
     *
     * @param dataDTO
     * @return
     */
    @Override
    public ResultDTO updatefinance_budgetInfo(finance_budgetPullDTO dataDTO) {


        /**
         * 先删除类别表
         *
         * 再进行添加
         */
        ResultDTO resultss = null;
        String finance_budget_id = dataDTO.getFinance_budget_id();
        FinanceBudgetPullDTO financeBudgetPullDTO = new FinanceBudgetPullDTO();
        financeBudgetPullDTO.setFinance_budget_id(finance_budget_id);
        financeBudgetsResource.deleteFinanceBudgetInfo(financeBudgetPullDTO);
        //  if(resultss!=null){
        if (null != dataDTO.getFinancetype() && !"".equals(dataDTO.getFinancetype())) {
            String financetype = dataDTO.getFinancetype();
            try {
                JSONArray array = new JSONArray(financetype);
                if (array != null && array.length() > 0) {
                    List<finance_budgetPullDTO> result = new ArrayList<finance_budgetPullDTO>();
                    for (int i = 0; i < array.length(); i++) {
                        JSONObject jsonObject = array.getJSONObject(i);
                        String id = jsonObject.getString("id");
                        String name = jsonObject.getString("name");
                        String number = jsonObject.getString("selectnumber");
                        String price = jsonObject.getString("price");
                        FinanceBudgetPullDTO financeBudgetPullDTO1 = new FinanceBudgetPullDTO();
                        financeBudgetPullDTO1.setFinance_budget_id(dataDTO.getFinance_budget_id());
                        financeBudgetPullDTO1.setOffice_id(id);
                        financeBudgetPullDTO1.setOffice_name(name);
                        financeBudgetPullDTO1.setOfficetools_number(number);
                        financeBudgetPullDTO1.setOfficetools_price(price);
                        financeBudgetsResource.addFinanceBudgetInfo(financeBudgetPullDTO1);
                    }
                }

            } catch (Exception e) {
                e.printStackTrace();
            }

        }


        String sql_isHas = "select count(1) from finance_budget WHERE finance_budget_id='" + dataDTO.getFinance_budget_id() + "'";

        String exist_finance = getReportingToOne(sql_isHas);
        int resultls = 0;
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        //构建返回对象
        ResultDTO resultDTO = new ResultDTO();


        String time = df.format(new Date());
        if (!exist_finance.equals("0")) {
            String sql = " update finance_budget set update_time='" + df.format(new Date()) + "' ";
            if (null != dataDTO.getFinance_budget_note() && !"".equals(dataDTO.getFinance_budget_note())) {
                sql += ",finance_budget_note='" + dataDTO.getFinance_budget_note() + "'";
            }
            if (null != dataDTO.getIsverify() && !"".equals(dataDTO.getIsverify())) {
                sql += ",isverify='" + dataDTO.getIsverify() + "'";
            }
            if (null != dataDTO.getOffice_first() && !"".equals(dataDTO.getOffice_first())) {
                sql += ",office_first ='" + dataDTO.getOffice_first() + "'";
            }
            if (null != dataDTO.getOffice_second() && !"".equals(dataDTO.getOffice_second())) {
                sql += ",office_second ='" + dataDTO.getOffice_second() + "'";
            }

            sql += " where finance_budget_id='" + dataDTO.getFinance_budget_id() + "'";
            resultls = addReportingTo(sql);
        }
        if (resultls != 0) {
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(warnSrc.update_success);
            resultDTO.setStr(finance_budget_id);
            WorkFlowPullDTO workFlowPullDTO = new WorkFlowPullDTO();
            workFlowPullDTO.setWork_flow_formid(dataDTO.getFinance_budget_id());
            workFlowPullDTO.setWork_flow_approval(dataDTO.getReport_people());
            workFlowPullDTO.setWork_flow_approval_organization_id(dataDTO.getWork_flow_approval_organization_id());
            workFlowPullDTO.setIsverify("0");

            workFlowResource.updateWorkFlowInfo(workFlowPullDTO);//更新信息表

        } else {
            resultDTO.setStatus("90001");
            resultDTO.setDescritpion(warnSrc.update_fail);
        }

        return resultDTO;
    }


}


